from .items import UrlItem, Content_Item_YJ, Label_Item_YJ, Label_Item_YJ_Temp, Base_Img_Item, Base_HTML_Content_Item
from .easy_db_reslove.DB_operate_4_scrapy import DB_operate_4_scrapy_class
from . import database as db
from scrapy.exceptions import DropItem
from scrapy.pipelines import images
import scrapy
from urllib.request import urlretrieve
import os
import urllib.request
import time
import re

db_op = DB_operate_4_scrapy_class()
cursor = db.connection.cursor()

MAX_DB_CACHE_NUM = 500


class CTaogubaPipeline(object):

    def __init__(self):
        self.label_set = set()
        self.path = "G:\\taoguba\\picture_YJ"  # 图片保存路径
        self.page = 0  # 图片名我用数字代替
        if not os.path.exists(self.path):  # 检查文件是否存在
            os.mkdir(self.path)
        self.path = "G:\\taoguba\\picture_SH"  # 图片保存路径
        if not os.path.exists(self.path):  # 检查文件是否存在
            os.mkdir(self.path)

        self.content_cache_4_db = []
        self.content_item = None #保存当前爬取的内容的主体部分，item 是Scrapy.Item的子类
        self.spider = None

    def open_spider(self, spider):
        self.spider = spider

    def close_spider(self, spider):
        if self.content_item:
            self.clear_content_cache(self.content_item)

    def get_insert_pre_sql_of_tingpan(self):
        if self.spider.name == 'taogu_yj':
            return "insert into taoguba_tingpan_yj  "
        elif self.spider.name == 'taogu_sh':
            return "insert into taoguba_tingpan_sh "
        return None

    def clear_content_cache(self, item, pre_sql=None):
        """清空内容缓存信息并刷新到数据库中
        Args:
            item: 需要插入的元素，这里的元素限定为Scrapy.Item()的子类
            pre_sql: 前面的插入语句
        Returns:
            Nano
            """
        content_temp = self.content_cache_4_db[:MAX_DB_CACHE_NUM]
        content_temp_value_list = list();
        for content in content_temp:
            content_temp_value_list.append(tuple(content.values()))

        suffix_sql = db_op.insert_one_item_sql(item)
        if not pre_sql:
            pre_sql = self.get_insert_pre_sql_of_tingpan()

        sql = pre_sql + suffix_sql
        print("executemany sql : %s ; value_list : %s" % (sql, content_temp_value_list))
        cursor.executemany(sql, content_temp_value_list)
        db.connection.commit()
        self.content_cache_4_db = self.content_cache_4_db[MAX_DB_CACHE_NUM:]
        # print("sql:%s : values:%s" %(sql,item))

    def get_label_table_name(self):
        """ 通过爬虫名称获取对应的表名称

        Returns:
            返回对应的表名称
        """
        if self.spider.name == 'taogu_yj':
            return "taoguba_label_yj  "
        elif self.spider.name == 'taogu_sh':
            return "taoguba_label_sh "
        return None

    def process_item(self, item, spider):
        """处理全部返回的主要元素的主要函数，通过isinstance 来仲裁调用的函数的不同"""
        # 插入链接数据
        pass
        # if isinstance(item, UrlItem):
        #     if spider.name == 'taoguba_link_sh':
        #         suffix_sql = db_op.insert_one_item_sql(item)
        #         pre_sql = "insert into TB_URLS_sh "
        #         sql = pre_sql + suffix_sql
        #         # print("sql:%s" % (sql))
        #         values = tuple(item.values())
        #         cursor.execute(sql, values)
        #         db.connection.commit()  # 进行提交操作
        #
        #     if spider.name == 'taoguba_link_yj':
        #         suffix_sql = db_op.insert_one_item_sql(item)
        #         pre_sql = "insert into TB_URLS_yj "
        #         sql = pre_sql + suffix_sql
        #         # print("sql:%s" % (sql))
        #         values = tuple(item.values())
        #         cursor.execute(sql, values)
        #         db.connection.commit()  # 进行提交操作
        #
        # elif isinstance(item, Content_Item_YJ):
        #     print("attack content item")
        #     self.content_item = item
        #     self.content_cache_4_db.append(item)
        #     if len(self.content_cache_4_db) > MAX_DB_CACHE_NUM:
        #         self.clear_content_cache(item)
        #
        # elif isinstance(item, Label_Item_YJ_Temp):
        #     cur_date = item['rec_time']
        #     label_items = []
        #     if item['labs']:  # 可能不进行赋值
        #         for it in item['labs']:
        #             table_name = self.get_label_table_name();
        #
        #             new_it = Label_Item_YJ()
        #             new_it['rec_time'] = cur_date
        #             new_it['lab'] = it
        #             label_items.append(new_it)
        #             # print(label_items)
        #             has_this_lab_sql = "select count(*) from %s where lab " % table_name + " = %s"
        #             cursor.execute(has_this_lab_sql, new_it['lab'])
        #             has_this_lab = True if cursor.fetchone()['count(*)'] > 0 else False
        #
        #             if not has_this_lab:
        #                 insert_sql = "insert into %s " % table_name + db_op.insert_one_item_sql(new_it)
        #                 cursor.execute(insert_sql, tuple(new_it.values()))
        #                 db.connection.commit()
        #             else:
        #                 is_later_than_pre = "select count(*) from %s where rec_time > " % table_name + " %s;"
        #                 cursor.execute(is_later_than_pre, new_it['rec_time'])
        #                 is_later_than_pre = True if cursor.fetchone()['count(*)'] > 0 else False
        #                 if is_later_than_pre:
        #                     #     需要进行更新
        #                     old_item = Label_Item_YJ()
        #                     old_item['lab'] = new_it['lab']
        #                     update_sql, values = db_op.update_one_by_single_older(new_item=new_it, old_item=old_item,
        #                                                                           table_name=table_name)
        #
        #                     cursor.execute(update_sql, values)
        #                     db.connection.commit()
        #                     print("更新 label :", new_it)
        #                 else:
        #                     #             不做任何事情
        #                     pass

        # elif isinstance(item, Base_Img_Item):
        #     down_src = item['download_url']
        #     print("img:%s" % item)
        #     try:  # 下载图片方法一，在使用urlretrieve直接下载，这个代码量少
        #         dir_pre = ''
        #         if spider.name == 'taogu_yijian':
        #             dir_pre = "G:\\taoguba\\picture_YJ"
        #         else:
        #             dir_pre = "G:\\taoguba\\picture_SH"
        #         # dir = ''
        #         # dir = self.get_success_picture_path(dir_pre, item['pre_str'])
        #         dir = dir_pre + "\\"+ re.sub(r'[\\/:*?"<>|]', '-', item['pre_dir'])
        #         print("save dir  : dir",dir)
        #         if not os.path.exists(dir):  # 检查文件是否存在
        #             os.mkdir(dir)
        #         dir += "\\" + str(self.page) + '.jpg'
        #         urlretrieve(down_src, dir)
        #         self.page += 1
        #     except Exception as e:
        #         print('Handling run-time error:', e)
        # elif isinstance(item, Base_HTML_Content_Item):
        #     try:  # 下载图片方法一，在使用urlretrieve直接下载，这个代码量少
        #         dir_pre = ''
        #         if spider.name == 'taogu_yijian':
        #             dir_pre = "G:\\taoguba\\picture_YJ"
        #         else:
        #             dir_pre = "G:\\taoguba\\picture_SH"
        #         dir = ''
        #         dir = self.get_success_picture_path(dir_pre, item['pre_dir'])
        #         self.mkdir_if_not_exist(dir)
        #
        #         dir += "\\" + '1.txt'
        #         self.save_text_2_di(item['parsed_content'], dir)
        #     except Exception as e:
        #         print('Handling run-time error:', e)


    def mkdir_if_not_exist(self, file_path):
        if not os.path.exists(file_path):  # 检查文件是否存在
            os.mkdir(file_path)

    def get_success_picture_path(self, pre, suffix):
        if not pre or not suffix:
            return None
        suffix = re.sub('[\/:*?"<>|]', '-', suffix)
        return pre + "\\" + suffix

    def save_text_2_di(self, str, save_path):
        file = open(save_path, encoding='utf8', mode='w');
        file.write(str)
        file.close()
